package cn.sfkj.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;


import java.util.*;
import javax.naming.*;
import javax.sql.DataSource;

import oracle.sql.BLOB;


//import com.sun.xml.internal.ws.wsdl.writer.document.Service;
public class DBAccess {
    private String jndiName;
    DataSource ds;
    Context ctx;
    private static final ThreadLocal<Connection> connectionHolder = new ThreadLocal<Connection>();
    public DBAccess(String jndiName) {
    	ds = null;
        ctx = null;
        this.jndiName = null;
        this.jndiName = jndiName;
        try {
        	ctx = new InitialContext();
        	ds = (DataSource)ctx.lookup(jndiName);
            ctx.close();
        } catch (NamingException ex) {
           ex.printStackTrace();
        }
    }
    
    public Connection getConnection() {
        Connection con;
        for(con = null; con == null;)
        	try {
        		con = ds.getConnection();
            } catch (Exception ex) {
            	   ex.printStackTrace();
                return null;
            }
        return con;
    }
    
    public void BeginTransaction() {
        Connection con;
        try {
        	con = ds.getConnection();
            con.setAutoCommit(false);
            connectionHolder.set(con);
            
        } catch (SQLException ex) {
        	ex.printStackTrace();
        }
    }
    
    public Connection getTransaction() {
    	System.out.println("1========="+connectionHolder.get());
    	return connectionHolder.get();
    }
    
    public void updateDataTransaction(String sql) throws Exception {
    	PreparedStatement stmt = null;
    	String res = "";
    	Connection con = null;
    	try {
    		con = connectionHolder.get();
    		if(null != con) {
    		stmt = con.prepareStatement(sql);
    		stmt.executeUpdate();
    		stmt.close();
    		System.out.println("2========="+connectionHolder.get());
    		}
    	} catch (Exception ex) {
			stmt.close();
			connectionHolder.get().rollback();
			endTransaction();
			System.out.println("发生严重错误!事务处理失败!");		
    	} finally {
			if (stmt != null) {  
				stmt.close();  
           	}  
        }
    }
    
    public void endTransaction() throws Exception {
    	Connection con = connectionHolder.get();
		try {
			if (con != null && !con.isClosed()) 
        	{  
        		con.commit();
        	
        	}  	
        } catch (SQLException ex) {
        	try{
        		if(con!=null) {
        			con.rollback();
        		}
        		System.out.println("发生严重错误!事务处理失败!");
        		ex.printStackTrace();
        	}catch(Exception sa) {
        		ex.printStackTrace();
        	}
        } finally {
        	if (con != null && !con.isClosed()) {  
        		con.close();  
        	}  
        	connectionHolder.remove();
        	System.out.println("3========="+connectionHolder.get());
        }
    }

    public Vector select(String sql)
        	throws Exception {
        Vector rows;
        rows = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            for(rs = stmt.executeQuery(); rs.next();) {
                Vector line = new Vector();
                int i = 0;
                try {
                    do {
                    	String value = rs.getString(++i);
                        if(value != null) {
                            line.addElement(value);
                        } else {
                        	line.addElement(null);
                        }
                    } while(true);
                } catch (Exception ex) {
                    rows.addElement(line);
                }
            }
        } catch (Exception ex) {
            throw ex;
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch(Exception ex) {
                throw ex;
            }
        }
        if(rows.size() == 0) {
        	return null;
        } else {
            return rows;
        }
    }

    public String selectOne(String sql)
        	throws Exception {
        Vector v = null;
      
        try {
            v = select(sql, 1, 1);
        } catch (Exception ex) {
            throw ex;
        }
        if(v == null) {
        	return null;
        } else {
        	return v.elementAt(0).toString();	
        }
    }

    public void insert(String sql)
        throws Exception {
        updateData(sql);
    }

    public void update(String sql)
        throws Exception {
        updateData(sql);
    }

    public void delete(String sql)
        throws Exception {
        updateData(sql);
    }

    private Vector select(String sql, int lineNum, int rowNum)
        	throws Exception {
    	Vector rows;
        rows = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            rs = stmt.executeQuery();
            if(rowNum == 1) {
                if(rs.next()) {
                    for(int i = 0; i < lineNum; i++) {
                        String value = rs.getString(i + 1);
                        if(value != null){
                        	rows.addElement(value);
                        } else {
                            rows.addElement(null);
                        }
                    }
                }
            } else {
            	while(rs.next()) {
                    Vector line = new Vector();
                    for(int i = 0; i < lineNum; i++)  {
                        String value = rs.getString(i + 1);
                        if(value != null) {
                            line.addElement(value);
                        } else {
                        	line.addElement(null);
                        }
                    }
                    rows.addElement(line);
                    if(--rowNum == 0) break;
                }
            }
        } catch(Exception ex) {
            throw ex;
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch(Exception ex) {
                throw ex;
            }
        }
        if(rows.size() == 0) {
        	return null;
        } else {
        	return rows;
        }
    }
    
    public boolean selectBlob(String sql,String file) throws Exception {
    	boolean flag = true;
    	FileOutputStream w =null;
    	InputStream in = null;
    	Connection con = null;
    	PreparedStatement stmt = null;
    	ResultSet rs = null;
    	try {
    		con = getConnection();
    		stmt = con.prepareStatement(sql);
    		rs = stmt.executeQuery();
    		if(rs.next()) in =  rs.getBinaryStream(1); 
    		w = new FileOutputStream(file); 
    		byte c[] = new byte[1024]; 
			int r = 0; 
			while ((r = in.read(c)) != -1){ 
				w.write(c,0,r); 
			}  
			
        }   
    	catch(Exception ex)
    	{
    		flag=false;
    		ex.getMessage();
    	}
    	finally
    	{
    		w.close(); 
    		in.close(); 
    		rs.close();
    		stmt.close();
    		con.close();
    	}
    	return flag;
    }
    
    public boolean excuteBlob(String sql,FileInputStream file) throws Exception {
    	boolean v=true;
    	Connection con = null;
    	PreparedStatement stmt = null;
    	try
    	{
    		con = getConnection();
    		stmt = con.prepareStatement(sql);
    		stmt.setBinaryStream(1,file,file.available()); 
    		stmt.execute(); 
        }   
    	catch(Exception ex)
    	{
    		v=false;
    		throw ex;	
    	}
    	finally
    	{
    		try
    		{
    			stmt.close();
    			con.close();
    		}
    		catch(Exception ex)
    		{
    			throw ex;
    		}
    	}
    	return v;
    }
    
    
    public Vector selectOnePage(String sql, int pageNo, int numPerPage)
        throws Exception {
        Vector rows;
        rows = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            rs = stmt.executeQuery();
            int rowNo = 1;
            int totalPages = 0;
            int totalRecords;
            Vector line;
            for(totalRecords = 0; rs.next(); totalRecords++) {
                if(rowNo > (pageNo - 1) * numPerPage && rowNo <= pageNo * numPerPage) {
                    line = new Vector();
                    int i = 0;
                    try {
                        do {
                            String value = rs.getString(++i);
                            if(value != null)
                                line.addElement(value);
                            else
                                line.addElement(null);
                        } while(true);
                    } catch(Exception ex) {
                        rows.addElement(line);
                    }
                }
                rowNo++;
            }

            if(totalRecords % numPerPage == 0) {
            	totalPages = totalRecords / numPerPage;
            }  else {
            	totalPages = totalRecords / numPerPage;
            }
            line = new Vector();
            line.addElement(Integer.toString(totalPages));
            line.addElement(Integer.toString(totalRecords));
            rows.insertElementAt(line, 0);
        } catch (Exception ex) {
            throw ex;
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (Exception ex) {
                throw ex;
            }
        }
        return rows;
    }

    private void updateData(String sql)
        throws Exception {
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            Log.printEvent("您要更新数据库的SQL语句为\"" + sql + "\"");
            con = getConnection();
            stmt = con.prepareStatement(sql);
            stmt.executeUpdate();
            Log.printEvent("数据库更新成功！");
        } catch(Exception ex) {
            throw ex;
        } finally {
            try {
                stmt.close();
                con.close();
            } catch (Exception ex) {
                throw ex;
            }
        }
        return;
    }
   
    public List ExecQuery(String sql) throws Exception {
    	List list;
        list = new ArrayList();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            rs = stmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numCols = rsmd.getColumnCount();
            String tempValue = "";
            HashMap hm;
            for( ; rs.next(); list.add(hm)) {
                hm = new HashMap();
                for(int i = 1; i <= numCols; i++) {
                    tempValue = rs.getString(i);
                    if(tempValue == null) tempValue = "";
                    hm.put(rsmd.getColumnLabel(i).toLowerCase(), tempValue);
                }
            }
        } catch(Exception e) {
            System.out.println(e + "\n\nError sql is:\n" + sql);
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
                if(stmt != null) {
                	stmt.close();
                }
                if(con != null) {
                	try {
                        if(!con.isClosed()) {
                            con.close();
                        }
                    } catch(Exception e) {
                        Log.printEvent("链接池关闭失败!");
                    }
                }
            }
            catch(SQLException ex) {
                ex.printStackTrace();
            }
        }
        return list;
    }
}

